package com.dao.Fxy;

import com.bean.Fxy.Fangwu;
import com.bean.Fxy.Fangwutianja;
import com.util.lzj.DBUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
@Component
public class FangwData {


        /**
         * 显示页面
         * @return
         */
        public List<Fangwu> housesDao(){
            Connection conn=Mysql.getConnection();
            QueryRunner qr=new QueryRunner();
            List<Fangwu> list=null;
            String sql="select  t.ttname,u.uname,ho.hname,h.*  from houses h left join household ho  on h.aid = ho.aid join tenement t on h.tid=t.tid join  unit u on u.uid=h.uid";
            try{
                list=qr.query(conn,sql,new BeanListHandler<Fangwu>(Fangwu.class));
            }catch (Exception e){
                e.printStackTrace();
            }
            return list;
        }

        /**
         * 删除
         *
         * @param hid
         * @return
         */
        public int shanchuDao(int hid){
            Connection conn=Mysql.getConnection();
            QueryRunner qr=new QueryRunner();
            String sql="DELETE  FROM houses where hid=?";
            try{
              int i= qr.update(conn,sql,hid);
                return i;
            }catch (Exception e){
                e.printStackTrace();
            }
            return 0;
        }

        /**
         * 楼宇下拉
         */
        public List<Fangwu> louyuxialaDao(){
            QueryRunner qr=new QueryRunner();
            Connection conn=Mysql.getConnection();
            String sql="select * from tenement  ";
            try{
                return    qr.query(conn,sql,new BeanListHandler<Fangwu>(Fangwu.class));
            } catch (Exception e) {
                e.printStackTrace();
            }
            return null;
        }

        /**
         * 单元下拉
         */
        public List<Fangwu> danyuanxialaDao(Integer lylx){
            QueryRunner qr=new QueryRunner();
            Connection conn=Mysql.getConnection();
            String sql="SELECT u.uid,u.uname FROM tenement t INNER JOIN unit u on t.tid=u.tid where t.tid=?";
            try{
                return    qr.query(conn,sql,new BeanListHandler<Fangwu>(Fangwu.class),lylx);
            } catch (Exception e) {
                e.printStackTrace();
            }
            return null;
        }

        /**
         * 住户下拉
         */
        public List<Fangwu> zhuhiuxialaDao(){
            QueryRunner qr=new QueryRunner();
            Connection conn=Mysql.getConnection();
            String sql="SELECT * FROM household";
            try{
                return    qr.query(conn,sql,new BeanListHandler<Fangwu>(Fangwu.class));
            } catch (Exception e) {
                e.printStackTrace();
            }
            return null;
        }

        /**
         *添加
         * @param houses
         * @return
         */
        public  int tjDao(Fangwutianja houses){
            QueryRunner qr=new QueryRunner();
            Connection conn=Mysql.getConnection();
            String sql="INSERT INTO houses VALUES (null,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            Object[] objects=new Object[]{houses.getLyxl(),houses.getDyxl(),houses.getCengshu(),houses.getHanghao() ,houses.getJzmj(),houses.getTnmj(),houses.getGtmj(),houses.getFwlx(),
                    houses.getFwhx(),houses.getFwcx(),houses.getJfsj(),houses.getCqnx(),houses.getBeizhu(),houses.getZhuhua()
            };

            try{
                return  qr.update(conn,sql,objects);
            } catch (Exception e) {
                e.printStackTrace();
            }
            return 0;
        }

        /**
         * 修改页面赋值
         * @param td2
         * @return
         */
        public Fangwu xgDao(int td2) {

            QueryRunner qr = new QueryRunner();
            Connection conn =Mysql.getConnection();
            String sql = "select * from houses where hid=?";
            try {
                return qr.query(conn, sql, new BeanHandler<>(Fangwu.class),td2);
            } catch (Exception e) {
                e.printStackTrace();
            }
            return null;
        }

        /**
         * 确定修改页面
         */
        public int qrtxgDao(Fangwutianja houses ){
            Connection conn=Mysql.getConnection();
            QueryRunner qr=new QueryRunner();
            String sql="UPDATE houses SET tid = ?,Uid = ?,hfloor = ?,honame = ?,hbuild = ?,hinside = ?,hequally = ?,hmold = ?,htype = ?,hdirection = ? ,htime=?,hyear=?,hremarks=?,aid=? WHERE hid = ?";
            Object[] objects=new Object[]{houses.getLyxl(),houses.getDyxl(),houses.getCengshu(),houses.getHanghao() ,houses.getJzmj(),houses.getTnmj(),houses.getGtmj(),houses.getFwlx(),
                    houses.getFwhx(),houses.getFwcx(),houses.getJfsj(),houses.getCqnx(),houses.getBeizhu(),houses.getZhuhua(),houses.getHid()
            };
            try{
                int i=qr.update(conn,sql,objects);
                System.out.println(houses);
                return i;

            } catch (Exception e) {

                e.printStackTrace();
            }
            return 0;
        }

        /**
         * 综合查询
         * @param lycx
         * @param ducx
         * @return
         */

        public Object selectHorese(int lycx, int ducx) {
            Connection con=Mysql.getConnection();
            QueryRunner qr = new QueryRunner();
            String sql ;
            Object[] ob;
            sql="select  t.ttname,u.uname,ho.hname,h.*  from houses h left join household ho  on h.aid = ho.aid join tenement t on h.tid=t.tid join  unit u on u.uid=h.uid where h.tid=? and h.uid=?";
            ob = new Object[]{lycx,ducx};

            List<Fangwu> lbm ;
            try {
                lbm = qr.query(con,sql,ob,new BeanListHandler<Fangwu>(Fangwu.class));
                return lbm;
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return null;
        }

    }


